#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Project: pymysql3-1
# 首页火爆专区API数据整理


import json
import pymysql
import random


def extDictFromJson(jsonStr):
    dic = json.loads(jsonStr)
    return dic


def insertTarget(tgCursor, sql):
    try:
        tgCursor.execute(sql)
        # tgDb.commit()
    except Exception as e:
        # tgDb.rollback()
        print("Error: unable to fecth data:" + str(e))


db = pymysql.connect(host="localhost",
                     user="root",
                     password="password",
                     port=13306,  # 端口
                     database="resultdb",
                     charset='utf8')

cursor = db.cursor()


tgDb = pymysql.connect(host="localhost",
                       user="mall",
                       password="mall",
                       port=3306,
                       database="mall",
                       charset='utf8')

tgCursor = tgDb.cursor()

#sql = "SELECT result FROM bxHomePageBelowConten WHERE taskid = '{}'".format('0051055424271aaf255e1de14115bed8')
sql = "SELECT result FROM bxHomePageBelowConten"


# try:
#     # 执行SQL语句
#     cursor.execute(sql)
#     # 获取所有记录列表
#     results = cursor.fetchall()
#     for row in results:
#         taskId = row[0]
#         url = row[1]
#         # result = row[2]
#         updatetime = row[2]
#         # 打印结果
#         print("taskId={},url={},updatetime={}".format(taskId, url, updatetime))
# except Exception as e:
#     print("Error: unable to fecth data:" + str(e))


try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        for result in row:
            # result = row[0]
            resultStr = bytes(result).decode('utf-8')
            resDic = extDictFromJson(resultStr)
            # print("goodsInfo.goodsId={}, {}".format(resDic['goodsInfo']['goodsId'], random.randrange(100, 2000)))

            hotPotGoodsSQL = "insert into hot_pot_goods values ('{}','{}',{})" \
                .format('default-hot-pot',
                        resDic['goodsInfo']['goodsId'],
                        random.randrange(100, 2000)
                        )
            insertTarget(tgCursor, hotPotGoodsSQL)
            tgDb.commit()

except Exception as e:
    # tgDb.rollback()
    print("Error: unable to fecth data:" + str(e))

tgCursor.close()
tgDb.close()
cursor.close()
db.close()
